
IF EXISTS (SELECT 1
          FROM SYSOBJECTS
          WHERE  id = OBJECT_ID('USP_List_stp_auth_department1')
          AND TYPE IN ('P','PC'))
DROP PROCEDURE USP_List_stp_auth_department1
GO
/****** Object:  StoredProcedure [dbo].[USP_List_stp_auth_department1]    Script Date: 12/20/2010 16:29:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		AKE
-- Create date: 6:20 PM 8/25/2010
--- [USP_List_stp_auth_department1] 0,0,0,0,0
-- =============================================
CREATE PROCEDURE [dbo].[USP_List_stp_auth_department1]
	@staff_code int,
	@department_code int,
	@department_status int,
	@main_system_code int,
	@menu_system_code int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
    -- Modified by Boonyarit Yingvoragan
    -- Modified date 26/10/2553
    declare @master_department_level as integer
	declare @table as table(department_code int)
	declare @level as int
	
	-- if you want to select all department, use the staff code's department.
	if @department_code = 0
		select @department_code = department_code from staff where staff_code = @staff_code

	-- find out the department level of staff
	select @level = mas.master_department_level 
	from stp_department dep 
	inner join master_department mas on dep.master_department_code = mas.master_department_code
	inner join staff s on s.department_code = dep.department_code 
	where s.staff_code = @staff_code 

	-- if you are super admin, you can see every department.
	if @department_code = 0
	begin
		insert @table 
		select dep.department_code
		from stp_department dep 
		where department_code > 0
	end
	
	-- you are normal staff, you can see only your child department.
	if @department_code > 0
	begin
		insert @table select @department_code
		while @level < 10
		begin
			insert @table 
			select department_code 
			from stp_department 
			where department_parent in (select department_code from @table) 
			set @level = @level + 1
		end
	end
	
	-- =============================================
	-- Query data from authenticated department
	-- =============================================
	select	
			SeqNo = ROW_NUMBER() OVER (ORDER BY dept.department_code) ,
			dept_hirachy = dept.department_name ,--(select dbo.UFN_GetDepartmentAsHirachyByDepartmentCode(dept.department_code)),
			department_status,
			auth.department_code,
		 
			department_parent = 
			CASE 
			WHEN dept.department_parent is null THEN 0
			ELSE dept.department_parent
			END,
			 
			mas.master_department_level,
			sub.sub_menu_name,
			main_system_name,
			menu_system_name,
			mast.[type_name]
	from	stp_auth_department as auth
	join	stp_department as dept on auth.department_code = dept.department_code
	join	master_department as mas on mas.master_department_code = dept.master_department_code
	join	stp_sub_menu as sub on sub.sub_menu_code = auth.sub_menu_code
	join	stp_menu_system as menu on menu.menu_system_code = sub.menu_system_code
	join	stp_main_system as main on main.main_system_code = menu.main_system_code
	join	master_type as mast on mast.type_code = sub.type_code
	where	auth.department_code in  (select department_code from @table where department_code is not null and department_code > 0) and
			@department_status in (0,auth_department_status)
			and	@main_system_code in (0,main.main_system_code)
			and @menu_system_code in (0,sub.menu_system_code)
	group by auth.department_code, dept.department_code
	, seq_order, dept.department_name ,department_status,dept.department_parent
	, mas.master_department_level, main_system_name, menu_system_name, sub.sub_menu_name
	, mast.[type_name]

END

